
import pymssql
from DBUtils.PooledDB import PooledDB
import os
###
#pip install pymysql
#pip install DBUtils==1.3  --- 特别要注意
class TMssqlConnection:
    def __init__(self) -> None:
        self.key = ""

        self.host="meeting.xmgkfw.com"
        self.port = 42049
        self.userName = "sa"
        self.pwd = "Hhuc1115"
        self.dbName = "modbusTcp"
        self.maxNum = 10 
        self.minNum = 1
        self.sqlPath = ""

        self.pool = None
    def setConfig(self , host , port , uid , pwd , db , sqlPath):
        self.host = host
        self.port = port
        self.uid = uid
        self.pwd = pwd
        self.dbName = db
        self.sqlPath = sqlPath         
    def getConn(self):
        conn = None
        try:
            if self.pool == None:
                self.pool = PooledDB(
                    creator = pymssql ,
                    mincached  = 1 ,
                    maxcached  = 10 ,
                    host = self.host ,
                    port = self.port ,
                    database = self.dbName ,
                    user = self.userName ,
                    password = self.pwd 
                )
            conn = self.pool.connection()
            #conn.setAutoCommit(True)
        except Exception as er:
            print(er) 
        return conn

    def getSql(self , sqlKey):
        res=""
        try:
            filePath = os.path.join( self.sqlPath , sqlKey)
            if filePath.find(".txt")<0:
                filePath = filePath+".txt"
            if os.path.exists( filePath):
                with open(filePath, "r", encoding='utf-8') as f:
                    res = f.read()
            else:
                print('sqlKey('+sqlKey+') is error.')
        except Exception as er:
            print(er)
        return res
    def query( self , sql , params):
        res ={}
        rs = None
        try:
            conn = self.getConn()
            cursor = conn.cursor(as_dict=True)
            if params!=None:
                cursor.execute(sql , params)
            else:
                cursor.execute(sql) 
            
            rsList =[]

            if  cursor.description!=None: 
                rs = cursor.fetchall() 
                rsList.append(list(rs))
                while cursor.nextset():
                    rs = cursor.fetchall() 
                    rsList.append(list(rs))
                if len(rsList)==1 :
                    res = rsList[0]
                else:
                    res = rsList
                #res = list(rs)
            else:
                res={
                    "rowNum": cursor.rownumber ,
                    "rowCount": cursor.rowcount ,
                    "newId": cursor.lastrowid
                }   
            cursor.close()
            cursor = None
            conn.close()
            conn = None
        except Exception as er:
            print(er)
        return res
    def queryBySqlKey( self , sqlKey , params):
        rs = None
        try:
            sql = self.getSql(sqlKey)
            if sql!="":
                rs = self.query(sql , params)
        except Exception as er:
            print(er)
        return rs


class TMssql:
    def __init__(self) -> None:
        self.connections = {}
    def setConnections(self , connectionKey , host , port , uid , pwd , db , sqlPath):
        conn = None
        if connectionKey in  self.connections.keys():
            conn = self.connections[connectionKey]
        else:
            conn = TMssqlConnection()
        conn.key = connectionKey
        conn.setConfig(host , port , uid , pwd , db , sqlPath)
        self.connections[connectionKey] = conn
    def routerHandlerQuery(self, result , request , router):
        try:
            ps = router.getRequestParams(request)
            dbKey = ""
            sqlKey = ""
            params ={}
            for p in ps.keys():
                if p=='dbKey':
                    dbKey = ps["dbKey"]
                elif p=='sqlKey':
                    sqlKey = ps["sqlKey"]
                else:
                    params[p] = ps[p]
            
            if dbKey in self.connections.keys():
                client = self.connections[dbKey]
                sql = client.getSql(sqlKey)
                if sql!="":              
                    if len(params.keys())==0:
                        params=None
                    rs = client.query(sql , params)
                    result["datas"] = rs
                    result["status"] = 1
                else:
                    result["status"] = -2
                    result["error"] = "sqlKey is error"
            else:
                result["status"] = -1
                result["error"] = "dbKey is error" 
        except Exception as er:
            print(er)


mssql = TMssql()

mssql.setConnections("modbusTcp", 
    host="meeting.xmgkfw.com",
    port=42049,
    uid="sa",
    pwd="Hhuc1115",
    db="modbusTcp",
    sqlPath="D:/project/pysvr/datas/sqls/modbusTcp"
)
